Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# INSTAT

The INSTAT function checks whether a dimension or dimension surrogate value is in the current status list or whether a dimension value is in a valueset.

Return Value

BOOLEAN

YES if the value is in the current status list or in a valueset and NO if it is not.

Syntax

INSTAT(dimension, value)

Arguments

dimension

  The name of the dimension, dimension surrogate, or valueset.

value

  The dimension or dimension surrogate value you want to test, either a text literal (enclosed in single quotes) or an expression that specifies the value. To specify the value of a conjoint dimension or a concat dimension, enclose the value in angle brackets. For a conjoint dimension, separate the base dimension values with a comma and space. For a concat dimension, separate the base dimension and its value with a colon and a space.

Notes

Checking an Invalid Value

When you specify a dimension name and value in an INSTAT statement, Oracle OLAP tells you whether that value is in the current status list for that dimension. Conversely, theISVALUE function tells you whether an item is a value of a dimension, regardless of whether it is in the status. INSTAT produces an error when value is not a dimension value, but ISVALUE simply returns a value of `FALSE`.

Examples

Example 15-14 Using INSTAT With a Valueset

Assume that within your analytic workspace you have a `geog` dimension with the following definition and values.

    DEFINE geog TEXT DIMENSION
    GEOG
    --------------
    Austria
    Belgium
    Canada

Assume, also, that you define a `mygeogs` valueset and identify a value for that valueset using the following statements.

    DEFINE mygeogs VALUESET geog
    LIMIT mygeogs TO 'Belgium'

You cannot issue a REPORT on a valueset. If you attempt to, Oracle OLAP issues an error message.

    REPORT mygeogs
    ORA-34104: INSTATTEST!MYGEOGS is not a type of object that contains data values.

However, you can use the INSTAT function to display which values of a dimension are in a valueset.

    REPORT INSTAT (mygeogs, geog)

    GEOG            INSTAT (MYGEOGS, GEOG)
    -------------- ------------------------
    Austria                              no
    Belgium                             yes
    Canada                               no

Example 15-15 Checking Current Status

In the following example, a program accepts a value of the `month` dimension as an argument. The first lines of the program use INSTAT to check whether the dimension value that was passed as an argument is in the current status for `month`. When it is, the program calls a report program. When it is not, the program branches to its error-handling section.

    ARGUMENT onemonth month

    IF INSTAT(month onemonth)
       THEN sales_report
       ELSE GOTO error
    ...

Example 15-16 Using INSTAT When the Dimension is a Conjoint Dimension

When the dimension that you specify is a conjoint dimension, then the entire value must be enclosed in single quotes. For example, suppose the analytic workspace already has a `region` dimension and a `product` dimension. The `region` dimension values include `East`, `Central`, and `West`. The `product` dimension values include `Tents`, `Canoes`, and `Racquets`.

The following statements define a conjoint dimension, and add values to it.

    DEFINE reg.prod DIMENSION <geography product>
    MAINTAIN reg.prod ADD <'East', 'Tents'> <'West', 'Canoes'>

To specify base positions, use a statement such as the following.

    SHOW INSTAT(reg.prod '<1, 1>')
    YES

To specify base text values, use a statement such as the following.

    SHOW INSTAT(reg.prod '<\'East\', \'Tents\'>')
    YES

Example 15-17 Using INSTAT When the Dimension is a Concat Dimension

When the dimension that you specify is a concat dimension, then you must enclose the entire `<component dimension: dimension value>` pair in single quotes. The following statement defines a concat dimension that has as its base dimensions `region` and `product`.

    DEFINE reg.prod.ccdim DIMENSION CONCAT(region product)

A report of `reg.prod.ccdim` returns the following.

    REG.PROD.CCDIM
    ----------------------
    <region: East>
    <region: Central>
    <region: West>
    <product: Tents>
    <product: Canoes>
    <product: Racquets>

To specify a base dimension position, use a statement such as the following.

    SHOW INSTAT(reg.prod.ccdim '<product: 3>')
    yes

To specify base dimension text values, use a statement such as the following.

    SHOW INSTAT(reg.prod.ccdim '<product: Tents>')
    YES